﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using StockFinder.Model;

namespace StockFinder.DataAccess
{
    public class PriceBreachDataAccess
    {
        private const string GET_ALL_PRICEBREACHCONTENDERS = "dbo.[GetAllPriceBreachContenders]";
        private const string INSERT_PRICEBREACHCONTENDER = "dbo.InsertPriceBreachContender";
        private const string TRUNCATE_PRICEBREACHCONTENDER = "dbo.TruncatePriceBreachContender";

        public static void TruncatePriceBreachContender()
        {
            using (SqlConnection connection = new SqlConnection(ApplicationConfiguation.DatabaseConnectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand(TRUNCATE_PRICEBREACHCONTENDER, connection) { CommandType = System.Data.CommandType.StoredProcedure })
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        public static void InsertPriceBreachContender(string symbol, decimal price, string notes)
        {
            using (SqlConnection connection = new SqlConnection(ApplicationConfiguation.DatabaseConnectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand(INSERT_PRICEBREACHCONTENDER, connection) { CommandType = System.Data.CommandType.StoredProcedure })
                {
                    command.Parameters.AddWithValue("@Symbol", symbol);
                    command.Parameters.AddWithValue("@Price", price);
                    command.Parameters.AddWithValue("@Notes", notes);

                    command.ExecuteNonQuery();
                }
            }
        }

        public static List<PriceBreachContender> GetAllPriceBreachContenders()
        {
            List<PriceBreachContender> result = null;

            using (SqlConnection connection = new SqlConnection(ApplicationConfiguation.DatabaseConnectionString))
            {
                connection.Open();

                using (SqlCommand command = new SqlCommand(GET_ALL_PRICEBREACHCONTENDERS, connection) { CommandType = System.Data.CommandType.StoredProcedure })
                {
                    using (SqlDataReader dataReader = command.ExecuteReader())
                    {
                        result = new List<PriceBreachContender>();

                        if (dataReader.HasRows)
                        {
                            while (dataReader.Read())
                            {
                                var symbol = Convert.ToString(dataReader["Symbol"]);
                                var price = Convert.ToDecimal(dataReader["Price"]);
                                var notes = Convert.ToString(dataReader["Notes"]);

                                result.Add(new PriceBreachContender()
                                {
                                    Symbol = symbol,
                                    Price = price,
                                    Notes = notes
                                });
                            }
                        }
                    }
                }
            }

            return result;
        }
    }
}
